﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using System.Reflection;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    [Serializable]
    public sealed class TimeAttendantDAL : ITimeAttendantsDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public TimeAttendantDAL()
        {
        }


        public int GetMissWorkingDays(string month, string year, string emp_seq)
        {
            int result = 0;
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Month", month);
                dbManager.AddParameters(2, "@Year", year);
                dbManager.AddParameters(3, "@EMP_SEQ", emp_seq);

                strSQL = "SELECT COUNT(*) AS CNT_MISS"
                    + " FROM HRM_PS_TIME_ATTENDANTS"
                    + " WHERE RECORD_STATUS not like @RecordStatus"
                    + " AND EMP_SEQ = @EMP_SEQ"
                    + " AND MONTH(WORKING_DATE) = @Month"
                    + " AND YEAR(WORKING_DATE) = @Year"
                    + " AND (WORKING_STATUS = 'N')";
                  

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    result = rdr.GetInt32(0);
                }

                return result;

            }
            catch
            {
                return 0;
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public int GetActualWorkingDays(string month, string year, string emp_seq)
        {
            int result = 0;
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Month", month);
                dbManager.AddParameters(2, "@Year", year);
                dbManager.AddParameters(3, "@EMP_SEQ", emp_seq);

                strSQL = "SELECT COUNT(*) AS CNT_ACTUAL"
                    + " FROM HRM_PS_TIME_ATTENDANTS"
                    + " WHERE RECORD_STATUS not like @RecordStatus"
                    + " AND EMP_SEQ = @EMP_SEQ"
                    + " AND MONTH(WORKING_DATE) = @Month"
                    + " AND YEAR(WORKING_DATE) = @Year"
                    + " AND ((WORKING_STATUS not like 'N') AND (WORKING_STATUS not like 'A')"
                    + " AND (WORKING_STATUS not like '' OR WORKING_STATUS IS NULL))";

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                 while (rdr.Read())
                 {
                     result = rdr.GetInt32(0);                    
                 }

                return result;

            }
            catch
            {
                return 0;
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public IList<TimeAttendantInfo> GetTimeAtt_NonStatus(string month,string year)
        {
            List<TimeAttendantInfo> result = new List<TimeAttendantInfo>();
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Month", month);
                dbManager.AddParameters(2, "@Year", year);

                strSQL = "SELECT *"
                    + " FROM HRM_PS_TIME_ATTENDANTS"                       
                    + " WHERE RECORD_STATUS not like @RecordStatus"
                    + " AND (WORKING_STATUS = '' OR WORKING_STATUS IS NULL)";
                if (!String.IsNullOrEmpty(month)) strSQL += " AND MONTH(WORKING_DATE) = @Month";
                if (!String.IsNullOrEmpty(year)) strSQL += " AND YEAR(WORKING_DATE) = @Year";
                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                result = (List<TimeAttendantInfo>)CollectionHelper.ConvertTo<TimeAttendantInfo>(dtb);

                return result;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataSet LoadTimeAttendants(string EmployeeType, string WorkingDate, string WorkingJobSiteSeq, string EmpSequence)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@WorkingDate", string.IsNullOrEmpty(WorkingDate) ? (DateTime?)null : DateTime.Parse(WorkingDate));
                dbManager.AddParameters(1, "@WorkingJobSiteSeq", WorkingJobSiteSeq);
                dbManager.AddParameters(2, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(3, "@EmpSequence", EmpSequence);
                dbManager.AddParameters(4, "@EmployeeType", EmployeeType);

                strSQL = "SELECT t.WORKING_SEQ, t.WORKING_DATE, t.EMP_SEQ, t.WORKING_START"
                        + ", t.WORKING_END, t.WORKING_STATUS, t.REMARKS"
                        + ", CASE t.WORKING_STATUS WHEN 'W' THEN 'ปกติ' "
                            + " WHEN 'L' THEN 'สาย' "
                            + " WHEN 'A' THEN 'ลา' "
                            + " WHEN 'N' THEN 'ขาด' "
                            + " ELSE '' END AS WORKING_STATUS_DESCR"
                        + ", CONCAT(np.PREFIX_NAME, e.FIRST_NAME, '  ', e.LAST_NAME) AS EMP_NAME"
                        + ", e.POS_SEQ, p.POS_NAME, e.WORKING_JOB_SITE_SEQ, j.JOB_SITE_NAME"
                        + ", e.CLASS_SEQ, c.CLASS_NAME, p.POS_REF"
                    + " FROM HRM_PS_TIME_ATTENDANTS t"
                        + " LEFT JOIN HRM_PS_EMPLOYEES e ON t.EMP_SEQ = e.EMP_SEQ"
                        + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                        + " LEFT JOIN HRM_MP_JOB_SITES j ON e.WORKING_JOB_SITE_SEQ = j.JOB_SITE_SEQ"
                        + " LEFT JOIN HRM_MP_CLASSES c ON e.CLASS_SEQ = c.CLASS_SEQ"
                        + " LEFT JOIN HRM_MP_POSITIONS p ON e.POS_SEQ = p.POS_SEQ"
                    + " WHERE t.RECORD_STATUS not like @RecordStatus"
                    + (string.IsNullOrEmpty(EmployeeType) ? "" : " AND e.EMP_TYPE_SEQ = @EmployeeType")
                    + (string.IsNullOrEmpty(EmpSequence) ? "" : " AND t.EMP_SEQ = @EmpSequence")                    
                    + (string.IsNullOrEmpty(WorkingJobSiteSeq) ? "" : " AND j.JOB_SITE_FULL_PATH LIKE " + "'%/" + WorkingJobSiteSeq + "/%'")
                    + (string.IsNullOrEmpty(WorkingDate) ? "" : " AND t.WORKING_DATE = @WorkingDate");
                return dbManager.ExecuteDataSet(CommandType.Text, strSQL);
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public IList<TimeAttendantInfo> LoadTimeAttendants_NewMod(string WorkingDate, string EmpSequence)
        {
            string strSQL;
            List<TimeAttendantInfo> result = new List<TimeAttendantInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                //dbManager.AddParameters(0, "@WorkingDate", string.IsNullOrEmpty(WorkingDate) ? (DateTime?)null : DateTime.Parse(WorkingDate));
                dbManager.AddParameters(0, "@WorkingDate", WorkingDate);
                //dbManager.AddParameters(1, "@WorkingJobSiteSeq", WorkingJobSiteSeq);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@EmpSequence", EmpSequence);
                //dbManager.AddParameters(4, "@EmployeeType", EmployeeType);

                strSQL = "SELECT *"
                    + " FROM HRM_PS_TIME_ATTENDANTS"                      
                    + " WHERE RECORD_STATUS not like @RecordStatus"
                    + (string.IsNullOrEmpty(EmpSequence) ? "" : " AND EMP_SEQ = @EmpSequence")
                    + (string.IsNullOrEmpty(WorkingDate) ? "" : " AND WORKING_DATE = @WorkingDate");
                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                result = (List<TimeAttendantInfo>)CollectionHelper.ConvertTo<TimeAttendantInfo>(dtb);

                return result;

            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public IList<TimeAttendantInfo> GetEmpSeqDist(string month, string year)
        {
            string strSQL;
            
            IList<TimeAttendantInfo> result = new List<TimeAttendantInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                //dbManager.AddParameters(0, "@WorkingDate", string.IsNullOrEmpty(WorkingDate) ? (DateTime?)null : DateTime.Parse(WorkingDate));
                dbManager.AddParameters(0, "@Month", month);
                dbManager.AddParameters(1, "@Year", year);
                dbManager.AddParameters(2, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                //dbManager.AddParameters(3, "@EmpSequence", emp_seq);

                strSQL = "SELECT DISTINCT EMP_SEQ"
                    + " FROM HRM_PS_TIME_ATTENDANTS"
                    + " WHERE RECORD_STATUS not like @RecordStatus AND WORKING_STATUS not like ''"
               //     + (string.IsNullOrEmpty(emp_seq) ? "" : " AND EMP_SEQ = @EmpSequence")
                    + (string.IsNullOrEmpty(month) ? "" : " AND MONTH(WORKING_DATE) = @Month")
                    +(string.IsNullOrEmpty(year) ? "" : " AND YEAR(WORKING_DATE) = @Year");
                //DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                 IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                 while (rdr.Read())
                 {
                     TimeAttendantInfo info = new TimeAttendantInfo(rdr.GetInt32(0));
                     result.Add(info);
                 }
               // result = (List<TimeAttendantInfo>)CollectionHelper.ConvertTo<TimeAttendantInfo>(dtb);

                return result;

            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public string GetEmpSequence(string CodeScan)
        {
            string strSQL;
            string EmpSequence = "";

            try
            {               

                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@CodeScan", CodeScan);
                dbManager.AddParameters(1, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                DataSet ds = null;
                DataRowCollection drc;
                DataTable dt = new DataTable();
                strSQL = "SELECT EMP_SEQ"
                    + " FROM HRM_PS_EMPLOYEES"
                    + " WHERE CODE_SCAN = @CodeScan";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                if (ds.Tables.Count > 0)
                {                   
                    drc = ds.Tables[0].Rows;
                    EmpSequence = drc[0]["EMP_SEQ"].ToString();
                }
                return EmpSequence;
            }
            catch
            {
                return "";
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }        
        }
        
        public bool CheckDuplicate(string EmpSequence, string WorkingDate)
        {
            string strSQL;
            bool bOK = false;

            try
            {

                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@EmpSequence", EmpSequence);
                dbManager.AddParameters(1, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(2, "@WorkingDate", String.Format("{0:yyyy-MM-dd}", DateTime.Parse(WorkingDate)));
                DataSet ds = null;
                DataRowCollection drc;

                strSQL = "SELECT EMP_SEQ"
                    + " FROM HRM_PS_TIME_ATTENDANTS"
                    + " WHERE EMP_SEQ = @EmpSequence"
                        + " AND WORKING_DATE = @WorkingDate"
                        + " AND RECORD_STATUS = @ActiveRecord";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                if (ds.Tables.Count > 0)
                {
                    drc = ds.Tables[0].Rows;
                    bOK = drc.Count > 0;
                }                
                return bOK;
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

        }

        public bool CheckDupTimeAttMonly(string EmpSequence, string Month, string Year)
        {
            string strSQL;
            bool bOK = false;

            try
            {

                dbManager.Open();
                dbManager.CreateParameters(4);

                int i = Convert.ToInt32(Year);
                i = i < 2550 ? i += 543:i;
                Year = i.ToString();
                dbManager.AddParameters(0, "@EmpSequence", EmpSequence);
                dbManager.AddParameters(1, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(2, "@Month", Month);
                dbManager.AddParameters(3, "@Year", Year);
                DataSet ds = null;
                DataRowCollection drc;

                strSQL = "SELECT *"
                    + " FROM HRM_PS_TIME_ATT_MONTHLY"
                    + " WHERE EMP_SEQ = @EmpSequence"
                        + " AND WORKING_MONTH = @Month"
                        + " AND WORKING_YEAR = @Year"
                        + " AND RECORD_STATUS = @ActiveRecord";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                if (ds.Tables.Count > 0)
                {
                    drc = ds.Tables[0].Rows;
                    bOK = drc.Count > 0;
                }
                return bOK;
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

        }

        public int Insert(string WorkingDate, int EmpSeq, string WorkingStart, string WorkingEnd
                 , string WorkingStatus, string Remarks, int CreatedBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(9);

                dbManager.AddParameters(0, "@WorkingDate", string.IsNullOrEmpty(WorkingDate) ? (DateTime?)null : DateTime.Parse(WorkingDate));
                dbManager.AddParameters(1, "@EmpSeq", EmpSeq);
                dbManager.AddParameters(2, "@WorkingStart", string.IsNullOrEmpty(WorkingStart) ? (TimeSpan?)null : TimeSpan.Parse(WorkingStart));
                dbManager.AddParameters(3, "@WorkingEnd", string.IsNullOrEmpty(WorkingEnd) ? (TimeSpan?)null : TimeSpan.Parse(WorkingEnd));
                dbManager.AddParameters(4, "@WorkingStatus", WorkingStatus);
                dbManager.AddParameters(5, "@Remarks", Remarks);
                dbManager.AddParameters(6, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(7, "@CreatedBy", CreatedBy);
                dbManager.AddParameters(8, "@CreatedWhen", DateTime.Today);

                strSQL = "INSERT INTO HRM_PS_TIME_ATTENDANTS(WORKING_DATE, EMP_SEQ, WORKING_START"
                        + ", WORKING_END, WORKING_STATUS, REMARKS, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES(@WorkingDate, @EmpSeq, @WorkingStart, @WorkingEnd"
                        + ", @WorkingStatus, @Remarks, @RecordStatus, @CreatedBy, @CreatedWhen)";

                return dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                return 0;
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }        

        public int Update(int WorkingSeq, string WorkingDate, int EmpSeq, string WorkingStart
            , string WorkingEnd, string WorkingStatus, string Remarks, int UpdatedBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(9);

                dbManager.AddParameters(0, "@WorkingSeq", WorkingSeq);
                dbManager.AddParameters(1, "@WorkingDate", string.IsNullOrEmpty(WorkingDate) ? (DateTime?)null : DateTime.Parse(WorkingDate));
                dbManager.AddParameters(2, "@EmpSeq", EmpSeq);
                dbManager.AddParameters(3, "@WorkingStart", string.IsNullOrEmpty(WorkingStart) ? (TimeSpan?)null : TimeSpan.Parse(WorkingStart));
                dbManager.AddParameters(4, "@WorkingEnd", string.IsNullOrEmpty(WorkingEnd) ? (TimeSpan?)null : TimeSpan.Parse(WorkingEnd));
                dbManager.AddParameters(5, "@WorkingStatus", WorkingStatus);
                dbManager.AddParameters(6, "@Remarks", Remarks);
                dbManager.AddParameters(7, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(8, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_PS_TIME_ATTENDANTS"
                           + " SET WORKING_DATE = @WorkingDate"
                                + ", EMP_SEQ = @EmpSeq"
                                + ", WORKING_START = @WorkingStart"
                                + ", WORKING_END = @WorkingEnd"
                                + ", WORKING_STATUS = @WorkingStatus"
                                + ", REMARKS = @Remarks"
                                + ", UPDATED_BY = @UpdatedBy"
                                + ", UPDATED_WHEN = @UpdatedWhen"
                           + " WHERE WORKING_SEQ = @WorkingSeq";

                return dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                return 0;
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public int UpdateStatus(int WorkingSeq, string WorkingStatus, string Remarks, int UpdatedBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@WorkingSeq", WorkingSeq);
                dbManager.AddParameters(1, "@WorkingStatus", WorkingStatus);
                dbManager.AddParameters(2, "@Remarks", Remarks);
                dbManager.AddParameters(3, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(4, "@UpdatedWhen", DateTime.Today);


                strSQL = "UPDATE HRM_PS_TIME_ATTENDANTS"
                           + " SET WORKING_STATUS = @WorkingStatus"
                                + ", REMARKS = @Remarks"
                                + ", UPDATED_BY = @UpdatedBy"
                                + ", UPDATED_WHEN = @UpdatedWhen"
                           + " WHERE WORKING_SEQ = @WorkingSeq";

                return dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                return 0;
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public int Delete(int WorkingSeq, int UpdatedBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@WorkingSeq", WorkingSeq);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(3, "@UpdateWhen", DateTime.Today);

                strSQL = "UPDATE HRM_PS_TIME_ATTENDANTS"
                    + " SET RECORD_STATUS = @RecordStatus"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdateWhen"
                    + " WHERE WORKING_SEQ = @WorkingSeq";

                return dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                return 0;
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public DataSet LoadTimeAttMonthly(string empType, string WorkingJobSiteSeq, string year, string month)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@empType", empType);
                dbManager.AddParameters(1, "@WorkingJobSiteSeq", WorkingJobSiteSeq);
                dbManager.AddParameters(2, "@year", year);
                dbManager.AddParameters(3, "@month", month);
                dbManager.AddParameters(4, "@RecordStatus", SystemInfo._DeleteRecordStatus);

                //strSQL = "SELECT tm.working_year,tm.plan_working_day_count,tm.actual_working_day_count,tm.quota_vacation"
                //       + " ,tm.vacation_day_count,tm.late_day_count,tm.sick_day_count,tm.sick_count,tm.private_day_count, time_att_month_seq"
                //       + " ,tm.deliver_day_count,tm.edu_day_count,tm.ordain_day_count,"

                strSQL = "SELECT tm.working_year,tm.plan_working_day_count,tm.actual_working_day_count"
                       + " , 0 as quota_vacation , 0 as vacation_day_count, 0 as late_day_count, 0 as sick_day_count, 0 as sick_count"
                       + " , 0 as private_day_count, time_att_month_seq"
                       + " , 0 as deliver_day_count, 0 as edu_day_count, 0 as ordain_day_count,"

                        + "tm.time_att_month_seq AS WORKING_SEQ, tm.EMP_SEQ"                      
                        + ", CONCAT(np.PREFIX_NAME, e.FIRST_NAME, '  ', e.LAST_NAME) AS EMP_NAME"
                        + ", e.POS_SEQ, p.POS_NAME, e.WORKING_JOB_SITE_SEQ, j.JOB_SITE_NAME"
                        + ", e.CLASS_SEQ, c.CLASS_NAME, p.POS_REF"
                        + " , e.CLASS_SEQ, c.CLASS_NAME, p.POS_REF "
                        + ", CASE tm.WORKING_MONTH WHEN 1 THEN 'มกราคม'"
                                + " WHEN 2 THEN 'กุมภาพันธ์'"
                                + " WHEN 3 THEN 'มีนาคม'"
                                + " WHEN 4 THEN 'เมษายน'"
                                + " WHEN 5 THEN 'พฤษภาคม'"
                                + " WHEN 6 THEN 'มิถุนายน'"
                                + " WHEN 7 THEN 'กรกฎาคม'"
                                + " WHEN 8 THEN 'สิงหาคม'"
                                + " WHEN 9 THEN 'กันยายน'"
                                + " WHEN 10 THEN 'ตุลาคม'"
                                + " WHEN 11 THEN 'พฤศจิกายน'"
                                + " WHEN 12 THEN 'ธันวาคม'"
                                + " ELSE '' END AS MONTH_NAME"
                    + " FROM hrm_ps_time_att_monthly tm "
                        + " LEFT JOIN HRM_PS_EMPLOYEES e ON tm.EMP_SEQ = e.EMP_SEQ"
                        + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                        + " LEFT JOIN HRM_MP_JOB_SITES j ON e.WORKING_JOB_SITE_SEQ = j.JOB_SITE_SEQ"
                        + " LEFT JOIN HRM_MP_CLASSES c ON e.CLASS_SEQ = c.CLASS_SEQ"
                        + " LEFT JOIN HRM_MP_POSITIONS p ON e.POS_SEQ = p.POS_SEQ"
                    + " WHERE tm.RECORD_STATUS not like @RecordStatus"
                    + (string.IsNullOrEmpty(empType) ? "" : " AND e.EMP_TYPE_SEQ = @empType")
                    + (string.IsNullOrEmpty(WorkingJobSiteSeq) ? "" : " AND j.JOB_SITE_FULL_PATH LIKE " + "'%/" + WorkingJobSiteSeq + "/%'")
                    + (string.IsNullOrEmpty(year) ? "" : " AND tm.WORKING_YEAR = @year")
                    + (string.IsNullOrEmpty(month) ? "" : " AND tm.WORKING_MONTH = @month");

                return dbManager.ExecuteDataSet(CommandType.Text, strSQL);
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public int InsertTimeAttMonthly(string emp_seq, Nullable<int> working_month, Nullable<int> plan_working_day_count
            , Nullable<int> actual_working_day_count, Nullable<decimal> valid_absence_day_count
            , Nullable<decimal> invalid_absence_day_count, Nullable<int> late_day_count, Nullable<int> sick_day_count
            , Nullable<int> private_day_count, Nullable<int> vacation_day_count, Nullable<int> working_year, Nullable<int> sick_count
            , Nullable<int> quota_vacation, Nullable<int> deliver_day_count, Nullable<int> edu_day_count, Nullable<int> ordain_day_count
            , Nullable<int> temp_day_count, int CreatedBy, DateTime? CreatedWhen)
        {
            string strSQL;

            try
            {
                //working_year = ((working_year != null)&&(Convert.ToInt32(working_year) < 2550)) ? (Convert.ToInt32(working_year) + 543) : working_year;
                
                dbManager.Open();
                dbManager.CreateParameters(20);
                dbManager.AddParameters(0, "@emp_seq", emp_seq);
                dbManager.AddParameters(1, "@working_month", working_month);
                dbManager.AddParameters(2, "@plan_working_day_count", plan_working_day_count);
                dbManager.AddParameters(3, "@actual_working_day_count", actual_working_day_count);
                dbManager.AddParameters(4, "@valid_absence_day_count", valid_absence_day_count);
                dbManager.AddParameters(5, "@invalid_absence_day_count", invalid_absence_day_count);
                dbManager.AddParameters(6, "@late_day_count", late_day_count);
                dbManager.AddParameters(7, "@sick_day_count", sick_day_count);
                dbManager.AddParameters(8, "@private_day_count", private_day_count);
                dbManager.AddParameters(9, "@vacation_day_count", vacation_day_count);
                dbManager.AddParameters(10, "@working_year", working_year);
                dbManager.AddParameters(11, "@sick_count", sick_count);
                dbManager.AddParameters(12, "@quota_vacation", quota_vacation);
                dbManager.AddParameters(13, "@deliver_day_count", deliver_day_count);
                dbManager.AddParameters(14, "@edu_day_count", edu_day_count);
                dbManager.AddParameters(15, "@ordain_day_count", ordain_day_count);
                dbManager.AddParameters(16, "@temp_day_count", temp_day_count);
                dbManager.AddParameters(17, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(18, "@CreatedBy", CreatedBy);
                dbManager.AddParameters(19, "@CreatedWhen", CreatedWhen);

                strSQL = "INSERT INTO hrm_ps_time_att_monthly("
                        + "emp_seq"
                        + ", working_month"
                        + ", plan_working_day_count"
                        + ", actual_working_day_count"
                        + ", valid_absence_day_count"
                        + ", invalid_absence_day_count"
                        + ", late_day_count"
                        //+ ", sick_day_count"
                        //+ ", private_day_count"
                        //+ ", vacation_day_count"
                        //+ ", working_year"
                        //+ ", sick_count"
                        //+ ", quota_vacation"
                        //+ ", deliver_day_count"
                        //+ ", edu_day_count"
                        //+ ", ordain_day_count"
                        //+ ", temp_day_count"
                        + ", record_status"
                        + ", CREATED_BY"
                        + ", CREATED_WHEN)"
                        + " VALUES("
                        + "@emp_seq"
                        + ", @working_month"
                        + ", @plan_working_day_count"
                        + ", @actual_working_day_count"
                        + ", @valid_absence_day_count"
                        + ", @invalid_absence_day_count"
                        + ", @late_day_count"
                        //+ ", @sick_day_count"
                        //+ ", @private_day_count"
                        //+ ", @vacation_day_count"
                        //+ ", @working_year"
                        //+ ", @sick_count"
                        //+ ", @quota_vacation"
                        //+ ", @deliver_day_count"
                        //+ ", @edu_day_count"
                        //+ ", @ordain_day_count"
                        //+ ", @temp_day_count"
                        + ", @RecordStatus"
                        + ", @CreatedBy"
                        + ", @CreatedWhen)";

                return dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch(Exception ex)
            {
                return 0;
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }


        public int UpdateTimeAttMonthly(string emp_seq, Nullable<int> working_month, Nullable<int> plan_working_day_count
            , Nullable<int> actual_working_day_count, Nullable<decimal> valid_absence_day_count
            , Nullable<decimal> invalid_absence_day_count, Nullable<int> late_day_count, Nullable<int> sick_day_count
            , Nullable<int> private_day_count, Nullable<int> vacation_day_count, Nullable<int> working_year, Nullable<int> sick_count
            , Nullable<int> quota_vacation, Nullable<int> deliver_day_count, Nullable<int> edu_day_count, Nullable<int> ordain_day_count
            , Nullable<int> temp_day_count, int UpdatedBy, DateTime? UpatedWhen)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(20);
                dbManager.AddParameters(0, "@emp_seq", emp_seq);
                dbManager.AddParameters(1, "@working_month", working_month);
                dbManager.AddParameters(2, "@plan_working_day_count", plan_working_day_count);
                dbManager.AddParameters(3, "@actual_working_day_count", actual_working_day_count);
                dbManager.AddParameters(4, "@valid_absence_day_count", valid_absence_day_count);
                dbManager.AddParameters(5, "@invalid_absence_day_count", invalid_absence_day_count);
                dbManager.AddParameters(6, "@late_day_count", late_day_count);
                dbManager.AddParameters(7, "@sick_day_count", sick_day_count);
                dbManager.AddParameters(8, "@private_day_count", private_day_count);
                dbManager.AddParameters(9, "@vacation_day_count", vacation_day_count);
                dbManager.AddParameters(10, "@working_year", working_year); //thai
                dbManager.AddParameters(11, "@sick_count", sick_count);
                dbManager.AddParameters(12, "@quota_vacation", quota_vacation);
                dbManager.AddParameters(13, "@deliver_day_count", deliver_day_count);
                dbManager.AddParameters(14, "@edu_day_count", edu_day_count);
                dbManager.AddParameters(15, "@ordain_day_count", ordain_day_count);
                dbManager.AddParameters(16, "@temp_day_count", temp_day_count);
                dbManager.AddParameters(17, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(18, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(19, "@UpatedWhen", UpatedWhen);

                strSQL = "UPDATE hrm_ps_time_att_monthly SET emp_seq = @emp_seq"

                      //  + working_month != "" ? ", working_month = @working_month" : ""
                        +", plan_working_day_count = @plan_working_day_count"
                        +", actual_working_day_count = @actual_working_day_count"
                        +", valid_absence_day_count = @valid_absence_day_count"
                        +", invalid_absence_day_count = @invalid_absence_day_count"
                        +", late_day_count = @late_day_count"
                        //+", sick_day_count = @sick_day_count"
                        //+", private_day_count = @private_day_count"
                        //+", vacation_day_count = @vacation_day_count"
                     //   + working_year != "" ? ", working_year = @working_year" : ""
                        //+", sick_count = @sick_count"
                        //+", quota_vacation = @quota_vacation"
                        //+", deliver_day_count = @deliver_day_count"
                        //+", edu_day_count = @edu_day_count"
                        //+", ordain_day_count = @ordain_day_count"
                        //+", temp_day_count = @temp_day_count"
                        +", UPDATED_BY = @UpdatedBy"
                        +", UPDATED_WHEN = @UpatedWhen"
                        +" WHERE emp_seq = @emp_seq AND working_month = @working_month"
                        +" AND working_year = @working_year AND record_status = @RecordStatus";

                return dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                return 0;
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public int DeleteTimeAttMonthly(int Sequence, int UpdatedBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(3, "@UpdateWhen", DateTime.Today);

                strSQL = "UPDATE HRM_PS_TIME_ATT_MONTHLY"
                    + " SET RECORD_STATUS = @RecordStatus"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdateWhen"
                    + " WHERE TIME_ATT_MONTH_SEQ = @Sequence";

                return dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                return 0;
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        //IList<TimeAttendantInfo> ITimeAttendantsDAL.GetTimeAtt_NonStatus(string month, string year)
        //{
        //    throw new NotImplementedException();
        //}

        //IList<TimeAttendantInfo> ITimeAttendantsDAL.LoadTimeAttendants_NewMod(string WorkingDate, string EmpSequence)
        //{
        //    throw new NotImplementedException();
        //}

        //IList<TimeAttendantInfo> ITimeAttendantsDAL.GetEmpSeqDist(string month, string year)
        //{
        //    throw new NotImplementedException();
        //}
    }

    


}
